package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;
import com.groot.medical.entity.PaySetting;
import com.groot.medical.entity.PersonalFiles;

public class PaySettingDAOImpl implements PaySettingDAO {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public int getAllCounts() {
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(cureent_year) AS id FROM t_pay_setting where 1=1");
		//查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
        return count;

	}
	@Override
	public List<PaySetting> findPaySettingListByPage(int startIndex, int pageSize) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_pay_setting order by cureent_year desc limit ?,?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
		
			List<PaySetting> paySettings = new ArrayList<>();
			while(rs.next()){
				
			
				PaySetting paySetting = new PaySetting();
				paySetting.setCureentYear(rs.getString(1));
				paySetting.setStartTime(rs.getDate(2));
				paySetting.setEndTime(rs.getDate(3));
				paySetting.setPayMonry(rs.getString(4));
				
				
				paySettings.add(paySetting);
				
			}
			
			return paySettings;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	private java.sql.Date fomart(java.util.Date data){
		return new java.sql.Date(data.getTime());
	}
	@Override
	public void addPaySettingInfo(PaySetting paysetting) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="insert into t_pay_setting(cureent_year,start_time,end_time,pay_monry)values(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1,paysetting.getCureentYear());
			ps.setDate(2,fomart(paysetting.getStartTime()));
			ps.setDate(3, fomart(paysetting.getEndTime()));
			ps.setString(4, paysetting.getPayMonry());
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}
	@Override
	public PaySetting checkInPayTime(String nowTime,String years) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "SELECT * FROM t_pay_setting WHERE cureent_year = ? AND ? >= start_time AND ? <= end_time;";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, years);
			ps.setString(2, nowTime);
			ps.setString(3, nowTime);
			rs = ps.executeQuery();
			PaySetting paySetting = new PaySetting();
			while(rs.next()){
				
				paySetting.setCureentYear(rs.getString(1));
				paySetting.setStartTime(rs.getDate(2));
				paySetting.setEndTime(rs.getDate(3));
				paySetting.setPayMonry(rs.getString(4));
			}
			
			return paySetting;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	@Override
	public String findMoneyByCurrentYear(String nowTime) {
		String money=null;
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "SELECT pay_monry FROM t_pay_setting WHERE cureent_year=?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, nowTime);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				money=rs.getString(1);
				
			}
			
			return money;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	@Override
	public PaySetting findPaySettingByYear(String year) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "SELECT * FROM t_pay_setting WHERE cureent_year=?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, year);
			
			rs = ps.executeQuery();
			PaySetting paySetting = new PaySetting();
			while(rs.next()){
				
				paySetting.setCureentYear(rs.getString(1));
				paySetting.setStartTime(rs.getDate(2));
				paySetting.setEndTime(rs.getDate(3));
				paySetting.setPayMonry(rs.getString(4));
			}
			
			return paySetting;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	@Override
	public void editPaySettingInfo(PaySetting paysetting) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			//UPDATE student SET name='"+name+"', age=" + age+" where name='大红'";
			String sql="update t_pay_setting set start_time=?,end_time=?,pay_monry=? where cureent_year=?";
			ps=conn.prepareStatement(sql);
			ps.setDate(1, fomart(paysetting.getStartTime()) );
			ps.setDate(2, fomart(paysetting.getEndTime()));
			ps.setString(3,paysetting.getPayMonry());
			ps.setString(4,paysetting.getCureentYear());
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}
	}

	


